CREATE TABLE doz (
    ID SMALLINT AUTO_INCREMENT,
    Name VARCHAR(50),
    Date DATE,
    PRIMARY KEY (ID)
);

CREATE TABLE doz_teamstatus (
    ID TINYINT,
    Name VARCHAR(30),
    PRIMARY KEY (ID)
);

INSERT INTO doz_teamstatus VALUES
    (0, 'Pending Email Verification'),
    (1, 'Pending Admin Validation'),
    (2, 'Validated'),
    (3, 'Deleted');

CREATE TABLE doz_themes (
    ID TINYINT,
    Name VARCHAR(20),
    PRIMARY KEY (ID)
);

INSERT INTO doz_themes VALUES
    (0, NULL),
    (1, 'General'),
    (2, 'Specific');

CREATE TABLE doz_scoretypes (
    ID TINYINT,
    Name VARCHAR(20),
    PRIMARY KEY (ID)
);

INSERT INTO doz_scoretypes VALUES
    (0, 'Point Score'),
    (1, 'Percentage Score'),
    (2, 'Point Penalty'),
    (3, 'Percentage Penalty'),
    (4, 'Rank Weight');

CREATE TABLE doz_scoresets (
    ID SMALLINT AUTO_INCREMENT,
    Name VARCHAR(50),
    PRIMARY KEY (ID)
);

CREATE TABLE doz_teams (
    ID MEDIUMINT AUTO_INCREMENT,
    StatusID TINYINT,
    DozID SMALLINT,
    Name VARCHAR(50),
    Number MEDIUMINT,
    Email VARCHAR(50),
    Passkey BINARY,
    PRIMARY KEY (ID),
    FOREIGN KEY (StatusID) REFERENCES doz_teamstatus(ID) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (DozID) REFERENCES doz(ID) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE doz_users (
    ID INT AUTO_INCREMENT,
    ForumID INT,
    Name VARCHAR(50),
    PRIMARY KEY (ID),
    FOREIGN KEY (ForumID) REFERENCES ibf_members(id) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE doz_competitors (
    ID INT AUTO_INCREMENT,
    UserID INT NOT NULL,
    TeamID MEDIUMINT NOT NULL,
    IsAlternate BIT,
    PRIMARY KEY (ID),
    FOREIGN KEY (UserID) REFERENCES doz_users(ID) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (TeamID) REFERENCES doz_teams(ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE doz_judges (
    ID MEDIUMINT AUTO_INCREMENT,
    DozID SMALLINT,
    UserID INT,
    PRIMARY KEY (ID),
    FOREIGN KEY (DozID) REFERENCES doz(ID) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (UserID) REFERENCES doz_users(id) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE doz_topics (
    ID MEDIUMINT AUTO_INCREMENT,
    DozID SMALLINT,
    ThemeID TINYINT,
    ScoresetID SMALLINT,
    Name VARCHAR(50),
    PRIMARY KEY (ID),
    FOREIGN KEY (DozID) REFERENCES doz(ID) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (ThemeID) REFERENCES doz_themes(ID) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (ScoresetID) REFERENCES doz_scoresets(ID) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE doz_scoresheets (
    ID MEDIUMINT AUTO_INCREMENT,
    TeamID MEDIUMINT,
    JudgeID MEDIUMINT,
    TopicID MEDIUMINT,
    Comments TEXT,
    PRIMARY KEY (ID),
    FOREIGN KEY (TeamID) REFERENCES doz_teams(ID) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (JudgeID) REFERENCES doz_judges(ID) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (TopicID) REFERENCES doz_topics(ID) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE doz_scorecats (
    ID SMALLINT AUTO_INCREMENT,
    ScoresetID SMALLINT NOT NULL,
    ScoretypeID TINYINT,
    Name VARCHAR(20),
    Points SMALLINT,
    Comments TEXT,  
    PRIMARY KEY (ID),
    FOREIGN KEY (ScoresetID) REFERENCES doz_scoresets(ID) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (ScoretypeID) REFERENCES doz_scoretypes(ID) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE doz_scores (
    ID INT AUTO_INCREMENT,
    ScorecatID SMALLINT,
    ScoresheetID MEDIUMINT,
    Points SMALLINT,
    Comments TEXT,
    PRIMARY KEY (ID),
    FOREIGN KEY (ScorecatID) REFERENCES doz_scorecats(ID) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (ScoresheetID) REFERENCES doz_scoresheets(ID) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE doz_games (
    ID MEDIUMINT AUTO_INCREMENT,
    TeamID MEDIUMINT,
    TopicID MEDIUMINT,
    Name VARCHAR(100),
    Filepath VARCHAR(200),
    PRIMARY KEY (ID),
    FOREIGN KEY (TeamID) REFERENCES doz_teams(ID) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (TopicID) REFERENCES doz_topics(ID) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE doz_news (
    ID INT AUTO_INCREMENT,
    DozID SMALLINT,
    Date DATE,
    Title VARCHAR(100),
    Content TEXT,
    PRIMARY KEy (ID),
    FOREIGN KEY (DozID) REFERENCES doz(ID) ON DELETE SET NULL ON UPDATE CASCADE
);